package com.xishu.dao.jpa;

import com.xishu.entity.shop.Reason;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

public interface ReasonDao extends JpaRepository<Reason, Long>, JpaSpecificationExecutor<Reason> {
    /**
     * 年月日选项，带id查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param idTypeList
     * @return
     */
    @Query(value = "select  count(*) as count ,  cancel_type as tmpId , FROM_UNIXTIME(create_time / 1000, :timeUnit) as x  " +
            "from reason m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.cancel_type in (:idTypeList)" +
            " group by m.cancel_type,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> idReport(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit,List<Long> idTypeList);

    /**
     * 年月日选项，不带id查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  count(*) as count ,  cancel_type as tmpId , FROM_UNIXTIME(create_time / 1000, :timeUnit) as x  " +
            "from reason m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime" +
            " group by m.cancel_type,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> report(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit);

    /**
     * 总计选项，带id查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param idTypeList
     * @return
     */
    @Query(value = "select  count(*) as count , cancel_type as tmpId " +
            "from reason m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.cancel_type in (:idTypeList)" +
            " group by m.cancel_type", nativeQuery = true)
    List<Tuple> idReportTotal(List<Long> shopIdList, Long startTime, Long endTime,List<Long> idTypeList);

    /**
     * 不带id查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select  count(*) as count , cancel_type as tmpId " +
            "from reason m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime" +
            " group by m.cancel_type", nativeQuery = true)
    List<Tuple> reportTotal(List<Long> shopIdList, Long startTime, Long endTime);

}
